#usage :  pip install openpyxl

#TODO:  Excecl.from_file(path).sheets[0]
#TODO:   Sheet.range(left, top, right, bottom) for a data Range  => for row in Range, for (index, cell) in Range
#TODO:   cell.value => SUM(A1:A5), how to get the number value
import os 
from openpyxl import load_workbook
from openpyxl import Workbook

from bcat.error import AbstractError

class ExcelError(AbstractError):
    def __init__(self, msg):
        super().__init__(msg)

class SheetNotExistsError(ExcelError):
    def __init__(self, sheet_names, file_path, msg=""):
        super().__init__(f"{sheet_names} not exists in the file {file_path}")
        self.__sheet_names = sheet_names
        self.__file_path = file_path

    def sheet_names(self):
        return self.__sheet_names
    def file_name(self):
        return self.__file_path

class Sheet(object):
    class Rows(object):
        #TODO:????
        def skip(self, count=0): #return Row
            pass 
    class Values(object):
        #TODO:????
        def skip(self, count=0): #return Row
            pass 
    class Cell(object):
        #TODO:????
        def skip(self, count=0): #return Row
            pass 

    def __init__(self, excel, name):
        self.__excel = excel
        self.__name = name
        self.__impl = excel.impl()[name]

    #TODO: return internal Rows Object
    def rows(self):
        return self.__impl.rows

    #TODO: return internal value Object
    def values(self):
        return self.__impl.values

    #TODO: return internal Cell object
    def cell(self, row, column):
        return self.__impl.cell(row, column)

    #True -> file saved ok 
    #False -> file not saved
    def save(self, path, force=False):
        return self.__excel.save(path)

    def set_name(self, name):
        if name != self.__name:
            self.__impl.title = name
            self.__name = name
        return self
    def name(self):
        return self.__name



class Excel(object):
    @staticmethod
    def from_file(path, data_only=True):
        if not os.path.exists(path):
            return None

        work_book = load_workbook(path, data_only=data_only)
        return Excel(work_book, path)

    @staticmethod
    def empty():
        work_book = Workbook()
        return Excel(work_book, None)

    def __init__(self, impl, file_path):
        self.__impl = impl
        self.__file_path = file_path

    def impl(self):
        assert(self.__impl is not None)
        return self.__impl

    def sheet_names(self):
        return self.__impl.sheetnames

    #exists sheet name
    def exists(self, name):
        return name in self.sheet_names()

    def sheet(self, name):
        if not self.exists(name):
            raise SheetNotExistsError(name, self.__file_path)
        return Sheet(self, name)

    def active(self):
        name = self.__impl.active.title
        return Sheet(self, name)

    def add_sheet(self, name):
        if not self.exists(name):
            self.__impl.create_sheet(name)
        return Sheet(self, name)

    def save(self, path, force=False):
        if not os.path.exists(path):  # file not-exist
            self.__impl.save(path)
            return True
        else: # file exist
            if force :
                self.__impl.save(path)
                return True
            else:
                return False




import unittest
class TestExcel(unittest.TestCase):
    def create_for_test(self, path):
        sheet = Excel.empty().active()
        sheet.set_name("First Sheet")
        for r in range(1, 5):
            sheet.cell(r, 1).value = 1
            sheet.cell(r, 2).value = 2

        sheet.save(path, True)
        self.assertTrue(os.path.exists(path))

    def test_reader(self):
        path = "./abc.xlsx"
        self.create_for_test(path)

        ex = Excel.from_file(path)
        self.assertIsNotNone(ex)

        sheet_name = "First Sheet"
        self.assertTrue(ex.exists(sheet_name))

        s = ""
        for (index, row) in enumerate(ex.sheet(sheet_name).values()):
            s = s + f"{index}:{row}, "
        self.assertEqual("0:(1, 2), 1:(1, 2), 2:(1, 2), 3:(1, 2), ", s)

        s = ""
        for row in ex.sheet(sheet_name).rows():
            for (col, cell) in enumerate(row):
                s = s + f"{col}:{cell.value}, "
        self.assertEqual("0:1, 1:2, 0:1, 1:2, 0:1, 1:2, 0:1, 1:2, ", s)

        ex = None

        #GC.collect()
        os.remove(path)

    def test_writer(self):
        ex_empty = Excel.empty();
        sheet = ex_empty.active()

        for r in range(0, 10):
            for c in range(0, 2):
                sheet.cell(r+1, c+1).value = r + c

        sheet = ex_empty.add_sheet("hello")
        for r in range(0, 10):
            for c in range(0, 2):
                sheet.cell(r+1, c+1).value = r + c

        path = "./new_excel.xlsx"
        if os.path.exists(path):
            os.remove(path)
        self.assertTrue(sheet.save(path))
        ex_empty = None

        os.remove(path)


